SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_CLIE_Q35]  
/*-------------------------------------------------------*/  
/*---Empresa               OFISIS S.A.                --*/  
/*---Cliente               SANTILLANA                 --*/  
/*---Sistema               Tesoreria                  --*/  
/*---M¢dulo                Empresa                    --*/  
/*---Programa              KARDEX          --*/  
/*---Script                SP_TMDOCU_CLIE_Q35.sql     --*/  
/*---Nombre SP             SP_TMDOCU_CLIE_Q35         --*/  
/*---Desarrollado por      Eduardo Vivar Pomiano      --*/  
/*---Fecha Creaci¢n        03/01/2001                 --*/  
/*---Base Datos            SQL SERVER     --*/  
/*---Versi¢n               2005                        --*/  
/*---Invoca a SP                                      --*/  
/*-------------------------------------------------------*/  
/*-------------------------------------------------------*/  
/*---Modificado 1 por     :D.CH.B     --*/  
/*---Fecha Modificaci¢n   :27/11/2001     --*/   
/*---Detalle Modificaci¢n : AGREGAR ANTICIPOS CLIENTES --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Drop Proc SP_TMDOCU_CLIE_Q35                      --*/  
/*---Grant Execute on SP_TMDOCU_CLIE_Q35 to public         --*/   
/*--- SP_TMDOCU_CLIE_Q35 "Where TMDOCC_GENE.CO_CLIE >= '00000000011' And TMDOCC_GENE.CO_CLIE <= '00000000011'",  
'', '2001/01/01','2003/05/07','2003/05/07', '1' ,'C', 'D'   --*/  
/*-------------------------------------------------------*/  
  
@ISCA_WHER_0001 VARCHAR(255),  
@ISCA_WHER_0002 VARCHAR(255),  
@IDFE_EMIS_INIC TD_DT_001,  
@IDFE_EMIS_FINA TD_DT_001,  
@IDFE_CANC TD_DT_001,  
@ISTI_ORDE TD_VC_001,  
@ISTI_AUXI_CLIE TD_VC_001,  
@ISTI_REPO  TD_VC_003  
  
AS  
  
 CREATE TABLE #TWDOCU_CLIE_Q35_1   
 ( CO_EMPR      varchar(3) NULL,  
   CO_TIPO_DOCU varchar(3)  NULL ,  
   NU_DOCU_CLIE varchar(20)  NULL,  
   CO_CLIE varchar(20) NULL ,  
   FE_REPO datetime NULL,  
   CO_MONE varchar(03) NULL,  
   FA_TIPO_CAMB numeric(9,4) NULL,  
   IM_CXCB numeric(16,4) NULL,  
   CO_CAJA varchar(8) NULL,  
   NU_AMAR varchar(8) NULL,  
   NU_CANJ varchar(8) NULL,  
   NU_RENO varchar(8) NULL,  
   CO_BANC varchar(3) NULL,  
   NU_CNTA_BANC varchar(100) NULL,      
   NU_COMP_CABA varchar(20) NULL,   
   IM_PAGO numeric(16,4) NULL,  
   TI_DETA varchar(2) NULL)  
  
CREATE TABLE #TWDOCU_CLIE_Q35_2   
 ( CO_EMPR      varchar(3) NULL,  
   CO_CLIE varchar(20) NULL,  
   CO_MONE      varchar(3) NULL,   
   IM_SAIN numeric(16,4) NULL)  
  
DECLARE   
  
@VSDE_LETR   TD_VC_100,  
@VSCO_EMPR TD_VC_002,  
@VSCO_CLIE   TD_VC_020,  
@VSNU_CANJ   TD_VC_008,  
@VSNU_RENO   TD_VC_008,  
@VSTI_ORDE   TD_VC_050,  
@VSFE_INIC TD_VC_020,  
@VSFE_FINA TD_VC_020,  
@VSFE_CANC TD_VC_020,  
@VSCA_WHER_BANC VARCHAR(600),  
@VSST_ANTC TD_VC_001,  
  
@CSCO_TIPO_DOCU TD_VC_003,  
@CSCO_EMPR   TD_VC_002,  
@CSCO_CLIE   TD_VC_020,  
@CSNU_CANJ   TD_VC_008,  
@CSNU_RENO   TD_VC_008,  
@CSNU_LETR_CLIE TD_VC_008,  
  
@OSTI_DOCU  TD_VC_003,  
@OSTI_DOCU_NCRE TD_VC_003,  
@OSTI_DOCU_0002 TD_VC_003,  
@OSTI_DOCU_CHEQ TD_VC_003   
  
  
EXEC SP_TTDOCU_CNTB_Q01 4, @OSTI_DOCU_NCRE OUTPUT  
  /* Devuelve el Tipo Documento CHEQUE */      
  Exec SP_TTDOCU_CNTB_Q01 29, @OSTI_DOCU_CHEQ Output     
  
SELECT @VSCA_WHER_BANC = REPLACE(@ISCA_WHER_0001+@ISCA_WHER_0002, "TMDOCC_GENE", "T2")  
SELECT @VSCA_WHER_BANC = REPLACE(@VSCA_WHER_BANC, "T2.CO_MONE", "T3.CO_MONE")  
SELECT @VSCA_WHER_BANC = REPLACE(@VSCA_WHER_BANC, "CO_CLIE", "CO_ENTI")  
SELECT @VSCA_WHER_BANC = REPLACE(@VSCA_WHER_BANC, "T2.TI_CLIE", "T8.TI_CLIE")  
  
  
IF PATINDEX("%ANT%", @ISCA_WHER_0001 + @ISCA_WHER_0002) > 0  
    SELECT @VSST_ANTC = 'S'  
ELSE  
    SELECT @VSST_ANTC = 'N'  
  
Select  @VSFE_INIC = Convert (varchar(10), @IDFE_EMIS_INIC, 103),  
 @VSFE_FINA = Convert (varchar(10), @IDFE_EMIS_FINA, 103),  
 @VSFE_CANC = Convert (varchar(10), @IDFE_CANC, 103)  
  
  
-- TIPOS DE DETALLE(TI_DETA = '1' AL '7')  
--1.- DOCS EMITIDOS EN ESOS MESES       
--2.- PAGOS BANCOS / CAJA  --3.- APLICACIONES DE NCR A DOCUMENTOS  
--4.- CANJES  
--5.- RENOVACIONES  
--6.- APLICACION DE DOC. A ANTICIPOS  
--7.- ANTICIPOS( CON APLICACION, SALDOS)  
--8.- ANTICIPOS( SIN APLICACIONES, SALDO = IMPORTE DEPOSITO )  
  
  
--1.- DOCS EMITIDOS EN ESOS MESES  
-- TMDOCU_CLIE  
  EXECUTE( "INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO,   
  CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)  
  Select TMDOCC_GENE.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU , TMDOCC_GENE.NU_DOCU_CLIE , TMDOCC_GENE.CO_CLIE, TMDOCC_GENE.FE_EMIS, 
  
  TMDOCC_GENE.CO_MONE, TMDOCC_GENE.FA_TIPO_CAMB,    
  ( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N') ) * TMDOCC_GENE.IM_TOTA,  
  '1'  
  From  TMDOCC_GENE, TTDOCU_CNTB T2 "   
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  And TMDOCC_GENE.TI_DOCU_ANTC = 'N'  
  And TMDOCC_GENE.FE_EMIS <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'  
  AND TMDOCC_GENE.CO_TIPO_DOCU = T2.TI_DOCU")  
  
  
--2.- PAGOS BANCOS / CAJA  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.FE_OPER, T2.CO_BANC, T2.NU_CNTA_BANC, T2.NU_COMP_BANC,  
 
  TMDOCC_GENE.CO_MONE, T2.FA_TIPO_CAMB,  
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N'))*( PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +  
      PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU ),   
  '2'  
  From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  And    T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'  
  And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And    T2.TI_SITU NOT IN ( 'ANU', 'EXT')   
  
  And    T1.CO_EMPR = T2.CO_EMPR    
  And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
  And    T1.CO_BANC = T2.CO_BANC    
  And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC    
  And    T1.AA_BNCO = T2.AA_BNCO  
  And    T1.MM_BNCO = T2.MM_BNCO   
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC   
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC  
  
  And    T1.CO_EMPR = T3.CO_EMPR    
  And    T1.CO_BANC = T3.CO_BANC    
  And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC  
  
  AND  T1.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  AND   T1.TI_DOCU_ENTI = TMDOCC_GENE.CO_TIPO_DOCU  
  AND   T1.NU_DOCU_ENTI = TMDOCC_GENE.NU_DOCU_CLIE  
  AND   TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU")  
  
   
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, CO_CAJA, NU_COMP_CABA,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.FE_OPER, T2.CO_CAJA, T2.NU_COMP_CAJA,   
  TMDOCC_GENE.CO_MONE, T2.FA_TIPO_CAMB,  
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N'))*( PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +  
      PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU),   
  '2'  
  From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  And    T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'  
  And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And    T2.TI_SITU NOT IN ( 'ANU', 'EXT')    
  
  And    T1.CO_EMPR = T2.CO_EMPR    
  And    T1.CO_CAJA = T2.CO_CAJA  
  And    T1.AA_CAJA = T2.AA_CAJA  
  And    T1.MM_CAJA = T2.MM_CAJA   
  And    T1.TI_MOVI = T2.TI_MOVI  
  And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA  
  
  And    T1.CO_EMPR = T3.CO_EMPR    
  And    T1.CO_CAJA = T3.CO_CAJA  
   
  And  T1.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  AND  T1.TI_DOCU_ENTI = TMDOCC_GENE.CO_TIPO_DOCU  
  And  T1.NU_DOCU_ENTI = TMDOCC_GENE.NU_DOCU_CLIE  
  
  AND   TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU")  
  
  
  
-- PAGOS A CHEQUES DE LA TMDOCU_CLIE EN BANCOS, CON TDCHEQ_BANC   
 EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,    
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  Select T1.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, T1.NU_CHEQ, T1.CO_ENTI, TMDOCC_GENE.FE_EMIS, T2.CO_BANC, T2.NU_CNTA_BANC, 
T2.NU_COMP_BANC,   
  TMDOCC_GENE.CO_MONE, T2.FA_TIPO_CAMB,  
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) *T1.IM_CHEQ ,  
  '2'  
  From  TDCHEQ_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  And    T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'  
  And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And    T2.TI_SITU = 'ACT'   
  
  And    T1.CO_EMPR = T2.CO_EMPR    
  And    T1.CO_BANC = T2.CO_BANC    
  And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC    
  And    T1.AA_BNCO = T2.AA_BNCO  
  And    T1.MM_BNCO = T2.MM_BNCO   
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC   
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC  
  
  And    T1.CO_EMPR = T3.CO_EMPR    
  And    T1.CO_BANC = T3.CO_BANC    
  And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC  
  
  AND  T1.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  AND   TMDOCC_GENE.CO_TIPO_DOCU  = '" + @OSTI_DOCU_CHEQ + "'
  AND   T1.NU_CHEQ = TMDOCC_GENE.NU_DOCU_CLIE  
   
  AND   TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU")  
  
  
--3.- APLICACIONES DE NOTA DE CREDITOS A DOCUMENTOS  
  
-- DOCUMENTOS  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_AMAR,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  SELECT T1.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_AMAR, T1.NU_AMAR,   
  TMDOCC_GENE.CO_MONE, T1.FA_TIPO_CAMB,   
  (PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * ISNULL(T2.IM_COBR_CDES, 0),  
  '3'  
  From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  AND  T1.CO_EMPR =   TMDOCC_GENE.CO_EMPR  
  And    T1.FE_AMAR  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  AND    T1.TI_SITU = 'ACT'  
  AND    T2.CO_TIDO_DEST = TMDOCC_GENE.CO_TIPO_DOCU   
  AND    T2.NU_DOCU_DEST = TMDOCC_GENE.NU_DOCU_CLIE   
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC  
  AND    T1.NU_AMAR = T2.NU_AMAR  
  AND    T1.FE_AMAR = T2.FE_AMAR  
  AND    T2.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  AND    T1.CO_CLIE = TMDOCC_GENE.CO_CLIE  
  AND    T2.CO_TIDO_DEST = TMDOCC_GENE.CO_TIPO_DOCU  
  AND    T2.NU_DOCU_DEST = TMDOCC_GENE.NU_DOCU_CLIE  
  AND  TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'   
  AND  TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU  
  AND  T5.ST_DOCU_0004 = 'N' ")  
  
  
-- NOTAS DE CREDITO APLICADAS   
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_AMAR,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  SELECT T1.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_AMAR, T1.NU_AMAR,  
  TMDOCC_GENE.CO_MONE, T1.FA_TIPO_CAMB,  
  (PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * ISNULL(T2.IM_COBR_CORI, 0),  
  '3'  
  From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  AND  T1.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  And    T1.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  AND    T1.TI_SITU = 'ACT'  
  AND    T2.CO_TIDO_ORIG = TMDOCC_GENE.CO_TIPO_DOCU   
  AND    T2.NU_DOCU_ORIG = TMDOCC_GENE.NU_DOCU_CLIE  
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC  
  AND    T1.NU_AMAR = T2.NU_AMAR  
  AND    T1.FE_AMAR = T2.FE_AMAR  
  AND    T2.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  AND    T1.CO_CLIE = TMDOCC_GENE.CO_CLIE  
  AND    T2.CO_TIDO_ORIG = TMDOCC_GENE.CO_TIPO_DOCU  
  AND    T2.NU_DOCU_ORIG = TMDOCC_GENE.NU_DOCU_CLIE  
     
  AND  TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'   
  AND   TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU  
  AND  T5.ST_DOCU_0004 = 'S' ")  
  
  
--4.- CANJES  
  
-- SOLO MUESTRO LOS DOCUMENTOS QUE SE CANJEAN, NO LAS LETRAS GENERADAS  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_CANJ,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  SELECT T1.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_CANJ, T1.NU_CANJ,   
  TMDOCC_GENE.CO_MONE, T1.FA_TIPO_CAMB,   
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) *   
  ( PatIndex(T2.ST_CIER_DOCU,'N') * T2.IM_CANJ_MDOC +   
    PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_DOCU ),  
  '4'  
  From  TCCANJ_CLIE T1 , TDCANJ_CLIE T2, TMDOCC_GENE, TTDOCU_CNTB T5 "   
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  And T1.CO_EMPR =   TMDOCC_GENE.CO_EMPR  
  And    T1.FE_CANJ  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  AND    T1.TI_SITU NOT IN ( 'ANU', 'EXT')    
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC  
  AND    T1.CO_CLIE = T2.CO_CLIE  
  AND    T1.NU_CANJ = T2.NU_CANJ  
  AND    T1.FE_CANJ = T2.FE_CANJ  
  AND    T2.CO_EMPR = TMDOCC_GENE.CO_EMPR  
  AND    T2.CO_CLIE = TMDOCC_GENE.CO_CLIE  
  AND    T2.CO_TIPO_DOCU = TMDOCC_GENE.CO_TIPO_DOCU   
  AND    T2.NU_DOCU_CLIE = TMDOCC_GENE.NU_DOCU_CLIE   
  
  AND    TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'   
  AND    TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU")  
  
  
--5.- RENOVACIONES  
  
-- SOLO MUESTRO LOS DOCUMENTOS QUE SE RENUEVAN, NO LAS LETRAS GENERADAS  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_RENO,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  SELECT T1.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_RENO, T1.NU_RENO,   
  TMDOCC_GENE.CO_MONE, T1.FA_TIPO_CAMB,   
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) *   
  ( PatIndex(T2.ST_CIER_DOCU,'N') * T2.IM_RENO_MDOC +   
    PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_DOCU ),  
  '5'  
  From   TCRENO_CLIE T1 , TDRENO_CLIE T2, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  AND  T1.CO_EMPR =   TMDOCC_GENE.CO_EMPR  
  And    T1.FE_RENO  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  AND    T1.TI_SITU NOT IN ( 'ANU', 'EXT')    
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC  
  AND    T1.CO_CLIE = T2.CO_CLIE  
  AND    T1.NU_RENO = T2.NU_RENO  
  AND    T1.FE_RENO = T2.FE_RENO  
  AND    T2.TI_SITU NOT IN ( 'ANU', 'EXT')    
  AND    T2.CO_CLIE = TMDOCC_GENE.CO_CLIE  
  AND    T2.CO_TIPO_DOCU = TMDOCC_GENE.CO_TIPO_DOCU   
  AND    T2.NU_DOCU_CLIE = TMDOCC_GENE.NU_DOCU_CLIE   
  
  AND  TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'   
  AND  TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU")  
  
  
--6.- APLICACION DE DOCUMENTOS A ANTICIPOS  
  
-- SOLO MUESTRO LOS DOCUMENTOS QUE SE APLICAN  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_AMAR,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  SELECT T1.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, TMDOCC_GENE.CO_CLIE, T1.FE_AMAR, T1.NU_AMAR,  
  TMDOCC_GENE.CO_MONE, T1.FA_TIPO_CAMB,  
  PatIndex(T2.ST_CIER_DOCU, 'N')*  
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T2.IM_CONV_DOCU +  
    PatIndex(T2.ST_CIER_DOCU, 'S')*  
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T2.IM_SALD_DOCU,  
  '6'  
  From   TCAMAR_ANTC T1 , TDAMAR_ANTC T2, TMDOCC_GENE, TTDOCU_CNTB T5 "  
  + @ISCA_WHER_0001 + @ISCA_WHER_0002 + "  
  And    T2.FE_AMAR  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  AND    T1.TI_SITU NOT IN ( 'ANU', 'EXT')   
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_UNID_CONC = T2.CO_UNID_CONC  
  AND    T1.NU_AMAR = T2.NU_AMAR  
  AND    T1.FE_AMAR = T2.FE_AMAR  
  AND  T2.CO_EMPR =   TMDOCC_GENE.CO_EMPR  
  AND    T2.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'   
  AND    T2.TI_DOCU_ENTI = TMDOCC_GENE.CO_TIPO_DOCU   
  AND    T2.NU_DOCU_ENTI = TMDOCC_GENE.NU_DOCU_CLIE   
  
  AND  TMDOCC_GENE.CO_ESTA_DOCU != 'ANU'   
  AND  TMDOCC_GENE.CO_TIPO_DOCU = T5.TI_DOCU")  
  
  
--7.- ANTICIPOS - PAGOS BANCOS   
  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  Select T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_BANC, T2.CO_ENTI, T2.FE_OPER, T2.CO_BANC, T2.NU_CNTA_BANC, T2.NU_COMP_BANC,  
 
  T3.CO_MONE, T2.FA_TIPO_CAMB, T2.IM_MOVI,   
  '7'  
  From  TCMOVI_BANC T2, TMCNTA_BANC T3,  TTTIPO_OPER T5, TMAUXI_EMPR T8 "  
  + @VSCA_WHER_BANC + "  
  And    T2.TI_MOVI_BANC = 'I'   
  And    T2.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'   
  And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And    T2.TI_SITU = 'ACT'   
  
  And    T2.CO_EMPR = T3.CO_EMPR    
  And    T2.CO_BANC = T3.CO_BANC    
  And    T2.NU_CNTA_BANC  = T3.NU_CNTA_BANC  
  
  AND  T2.CO_EMPR = T5.CO_EMPR  
  AND   T2.CO_TIPO_OPER = T5.CO_TIPO_OPER  
  AND    T5.ST_AUXI_0002 = 'S'  
  AND   T2.CO_EMPR = T8.CO_EMPR  
  AND  T2.TI_ENTI = T8.TI_AUXI_EMPR  
  AND  T2.CO_ENTI = T8.CO_AUXI_EMPR ")  
  
--8.- ANTICIPOS - PAGOS BANCOS   
-- ANTICIPO CON APLICACION   
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_AMAR,   
  CO_BANC, NU_CNTA_BANC, NU_COMP_CABA, CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)  
  Select T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_BANC, T2.CO_ENTI, T7.FE_AMAR, Max(T6.NU_AMAR),  
  Max(T6.CO_BANC), Max(T6.NU_CNTA_BANC), Max(T6.NU_COMP_BANC), T3.CO_MONE, MAX(T2.FA_TIPO_CAMB), 
ISNULL(SUM(T7.IM_DETA_NEGA),0),  
  '8'  
  From  TCMOVI_BANC T2, TMCNTA_BANC T3, TTTIPO_OPER T5, TCAMAR_ANTC T6, TDAMAR_ANTC T7,  
 TMAUXI_EMPR T8 "  
  + @VSCA_WHER_BANC + "  
  And    T2.TI_MOVI_BANC = 'I'   
  And    T2.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'   
  And    T7.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And    T2.TI_SITU = 'ACT'   
  
  And   T2.CO_EMPR = T3.CO_EMPR    
  And    T2.CO_BANC = T3.CO_BANC   
  And    T2.NU_CNTA_BANC  = T3.NU_CNTA_BANC  
  
  AND  T2.CO_EMPR = T5.CO_EMPR  
  AND   T2.CO_TIPO_OPER = T5.CO_TIPO_OPER  
  AND    T5.ST_AUXI_0002 = 'S'  
  
  AND  T2.CO_EMPR = T8.CO_EMPR  
  AND  T2.TI_ENTI = T8.TI_AUXI_EMPR  
  AND  T2.CO_ENTI = T8.CO_AUXI_EMPR  
  
  AND  T6.TI_SITU = 'ACT'  
  AND  T2.CO_EMPR = T6.CO_EMPR  
  And    T2.CO_BANC = T6.CO_BANC    
  And    T2.NU_CNTA_BANC  = T6.NU_CNTA_BANC  
  And    T2.AA_BNCO = T6.AA_BNCO  
  And    T2.MM_BNCO = T6.MM_BNCO  
  And    T2.NU_COMP_BANC = T6.NU_COMP_BANC  
  
  AND  T6.CO_EMPR = T7.CO_EMPR   
  AND  T6.CO_UNID_CONC = T7.CO_UNID_CONC   
  AND   T6.NU_AMAR = T7.NU_AMAR  
  AND   T6.FE_AMAR = T7.FE_AMAR  
  
  GROUP BY T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_BANC, T2.CO_ENTI, T3.CO_MONE,   
    T2.CO_BANC, T2.NU_CNTA_BANC, T2.NU_COMP_BANC , T7.FE_AMAR")  
  
--HAVING ( -1*(MAX(T2.IM_MOVI) - ISNULL( SUM( T7.IM_DETA_NEGA ), 0)) ) != 0  
  
/** ANTICIPOS X CAJA **/   
  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, CO_CAJA, NU_COMP_CABA,   
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)  
  Select TMDOCC_GENE.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE,  TMDOCC_GENE.CO_CLIE,   
  TMDOCC_GENE.FE_EMIS, TMDOCC_GENE.CO_CABA, TMDOCC_GENE.NU_COMP_CABA,  
  TMDOCC_GENE.CO_MONE, TMDOCC_GENE.FA_TIPO_CAMB, TMDOCC_GENE.IM_TOTA, '9'  
  From  TMDOCC_GENE "  
  + @ISCA_WHER_0001+@ISCA_WHER_0002 + "  
  AND  TMDOCC_GENE.TI_DOCU_ANTC = 'C'  
  And   TMDOCC_GENE.FE_EMIS  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  And   TMDOCC_GENE.CO_ESTA_DOCU = 'ACT' ")  
  
  
/** ANTICIPOS CAJA - APLICACION **/  
  
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q35_1   
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, NU_AMAR,   
  CO_CAJA, NU_COMP_CABA, CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)  
  Select TMDOCC_GENE.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, TMDOCC_GENE.CO_CLIE, T7.FE_AMAR, 
Max(T6.NU_AMAR),  
  Max(T6.CO_CAJA), Max(T6.NU_COMP_CAJA), TMDOCC_GENE.CO_MONE, MAX(T6.FA_TIPO_CAMB), ISNULL(SUM(T7.IM_DETA_NEGA), 0), '10'  
  From  TMDOCC_GENE, TCAMAR_ANTC T6, TDAMAR_ANTC T7 "  
  + @ISCA_WHER_0001+@ISCA_WHER_0002  + "  
  AND   TMDOCC_GENE.TI_DOCU_ANTC = 'C'  
  And    TMDOCC_GENE.CO_ESTA_DOCU = 'ACT'   
  And    T7.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)  
  
  AND  T6.TI_SITU = 'ACT'  
  AND  TMDOCC_GENE.CO_EMPR = T6.CO_EMPR   
  And    TMDOCC_GENE.CO_CLAV = T6.CO_CAJA+T6.AA_CAJA+T6.MM_CAJA+T6.NU_COMP_CAJA  
  
  AND  T6.CO_EMPR = T7.CO_EMPR   
  AND  T6.CO_UNID_CONC = T7.CO_UNID_CONC   
  AND   T6.NU_AMAR = T7.NU_AMAR  
  AND   T6.FE_AMAR = T7.FE_AMAR  
  
  GROUP BY TMDOCC_GENE.CO_EMPR, TMDOCC_GENE.CO_TIPO_DOCU, TMDOCC_GENE.NU_DOCU_CLIE, TMDOCC_GENE.CO_CLIE,  
    TMDOCC_GENE.CO_MONE, TMDOCC_GENE.CO_CLAV, T7.FE_AMAR")  
  
--SELECT * FROM #TWDOCU_CLIE_Q35_1  
--DETALLADO  
  
 -- SALDOS INICIALES  
 INSERT INTO #TWDOCU_CLIE_Q35_2(CO_EMPR, CO_CLIE,   
 CO_MONE, IM_SAIN)   
 SELECT CO_EMPR, CO_CLIE, CO_MONE,   
 SUM( ISNULL(IM_CXCB,0) ) - SUM( ISNULL(IM_PAGO,0) )  
 FROM #TWDOCU_CLIE_Q35_1  
 WHERE FE_REPO < @IDFE_EMIS_INIC  
 GROUP BY CO_EMPR, CO_CLIE, CO_MONE  
  
IF @ISTI_REPO = 'D'   
BEGIN  
  
 -- DATOS DE LETRAS DE CANJES Y RENOVACIONES  
 SELECT @VSCO_EMPR = ''  
 SELECT @VSCO_CLIE = ''  
 SELECT @VSNU_CANJ = ''  
 SELECT @VSDE_LETR = ''  
  
 DECLARE CU_TWDOCU_CLIE_Q35_1 CURSOR FOR  
 Select DISTINCT T1.CO_EMPR, T1.CO_CLIE, T1.NU_CANJ, T2.NU_LETR_CLIE  
 From  #TWDOCU_CLIE_Q35_1 T1, TDCANJ_CLIE T2  
 WHERE T1.CO_EMPR = T2.CO_EMPR  
 AND T1.CO_CLIE = T2.CO_CLIE  
 AND T1.NU_CANJ = T2.NU_CANJ  
 AND T1.FE_REPO = T2.FE_CANJ  
 AND T2.NU_LETR_CLIE IS NOT NULL  
 AND T1.FE_REPO >= @IDFE_EMIS_INIC  
 ORDER BY 1, 2, 3    
  
 Open CU_TWDOCU_CLIE_Q35_1  
 Fetch  CU_TWDOCU_CLIE_Q35_1 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_CANJ, @CSNU_LETR_CLIE  
 WHILE @@FETCH_STATUS = 0  
 Begin  
  
  IF @CSCO_EMPR + @CSCO_CLIE+ @CSNU_CANJ = @VSCO_EMPR + @VSCO_CLIE + @VSNU_CANJ   
   SELECT @VSDE_LETR = RTRIM(@VSDE_LETR) + ', ' + RTRIM(@CSNU_LETR_CLIE)  
    
  IF @CSCO_EMPR+@CSCO_CLIE+@CSNU_CANJ != @VSCO_EMPR+@VSCO_CLIE+@VSNU_CANJ    
  BEGIN  
   IF ISNULL(LEN(@VSDE_LETR),0) > 0   
    UPDATE #TWDOCU_CLIE_Q35_1  
    SET NU_CNTA_BANC = @VSDE_LETR  
    WHERE CO_EMPR = @VSCO_EMPR  
    AND CO_CLIE = @VSCO_CLIE  
    AND NU_CANJ = @VSNU_CANJ  
    AND FE_REPO >= @IDFE_EMIS_INIC  
     
   SELECT @VSDE_LETR = @CSNU_LETR_CLIE  
   SELECT @VSCO_EMPR = @CSCO_EMPR  
   SELECT @VSCO_CLIE = @CSCO_CLIE  
         SELECT @VSNU_CANJ = @CSNU_CANJ  
  END   
  Fetch  CU_TWDOCU_CLIE_Q35_1 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_CANJ, @CSNU_LETR_CLIE  
 End  
 Close CU_TWDOCU_CLIE_Q35_1  
 DEALLOCATE CU_TWDOCU_CLIE_Q35_1  
  
 UPDATE #TWDOCU_CLIE_Q35_1  
 SET NU_CNTA_BANC = @VSDE_LETR  
 WHERE CO_EMPR = @VSCO_EMPR  
 AND CO_CLIE = @VSCO_CLIE  
 AND NU_CANJ = @VSNU_CANJ  
 AND FE_REPO >= @IDFE_EMIS_INIC  
   
 SELECT @VSCO_EMPR = ''  
 SELECT @VSCO_CLIE = ''  
 SELECT @VSNU_RENO = ''  
 SELECT @VSDE_LETR = ''  
  
  
 DECLARE CU_TWDOCU_CLIE_Q35_2 CURSOR FOR  
 Select DISTINCT T1.CO_EMPR, T1.CO_CLIE, T1.NU_RENO, T2.NU_LETR_CLIE  
 From  #TWDOCU_CLIE_Q35_1 T1, TDRENO_CLIE T2  
 WHERE T1.CO_EMPR = T2.CO_EMPR  
 AND T1.CO_CLIE = T2.CO_CLIE  
 AND T1.NU_RENO = T2.NU_RENO  
 AND T1.FE_REPO = T2.FE_RENO  
 AND T2.NU_LETR_CLIE IS NOT NULL  
 AND T1.FE_REPO >= @IDFE_EMIS_INIC  
 ORDER BY 1, 2, 3  
  
 Open CU_TWDOCU_CLIE_Q35_2  
 Fetch  CU_TWDOCU_CLIE_Q35_2 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_RENO, @CSNU_LETR_CLIE  
 WHILE @@FETCH_STATUS = 0  
 Begin  
  
  IF @CSCO_EMPR+@CSCO_CLIE+@CSNU_RENO = @VSCO_EMPR+@VSCO_CLIE+@VSNU_RENO   
   SELECT @VSDE_LETR = RTRIM(@VSDE_LETR)+', '+RTRIM(@CSNU_LETR_CLIE)  
    
  
  IF @CSCO_EMPR+@CSCO_CLIE+@CSNU_RENO != @VSCO_EMPR+@VSCO_CLIE+@VSNU_RENO   
  BEGIN  
   IF ISNULL(LEN(@VSDE_LETR),0) > 0   
    UPDATE #TWDOCU_CLIE_Q35_1  
    SET NU_CNTA_BANC = @VSDE_LETR  
    WHERE CO_EMPR = @VSCO_EMPR  
    AND CO_CLIE = @VSCO_CLIE  
    AND NU_RENO = @VSNU_RENO  
    AND FE_REPO >= @IDFE_EMIS_INIC  
     
   SELECT @VSDE_LETR = @CSNU_LETR_CLIE  
   SELECT @VSCO_EMPR = @CSCO_EMPR  
         SELECT @VSCO_CLIE = @CSCO_CLIE  
         SELECT @VSNU_RENO = @CSNU_RENO  
  END   
  Fetch  CU_TWDOCU_CLIE_Q35_2 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_RENO, @CSNU_LETR_CLIE  
 End  
 Close CU_TWDOCU_CLIE_Q35_2  
 DEALLOCATE CU_TWDOCU_CLIE_Q35_2  
  
 UPDATE #TWDOCU_CLIE_Q35_1  
 SET NU_CNTA_BANC = @VSDE_LETR  
 WHERE CO_EMPR = @VSCO_EMPR  
 AND CO_CLIE = @VSCO_CLIE  
 AND NU_RENO = @VSNU_RENO  
 AND FE_REPO >= @IDFE_EMIS_INIC  
  
-------------------------  
   
 DELETE #TWDOCU_CLIE_Q35_1  
 WHERE FE_REPO < @IDFE_EMIS_INIC  
  
 IF @ISTI_ORDE = '1'  
   SELECT @VSTI_ORDE = ' ORDER BY 25, 1, 24, 4, 23, 5, 6, 26 '  
 ELSE  
   SELECT @VSTI_ORDE = ' ORDER BY 25, 1, 24, 4, 23, 26, 5, 6 '  
  
 EXECUTE('  
      SELECT T1.CO_EMPR,  T2.DE_NOMB, T1.CO_CLIE, T4.NO_AUXI,    
  T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, T1.CO_MONE, " ",   
  " ", CONVERT(VARCHAR(10), T1.FE_REPO, 103), ISNULL( T1.CO_BANC, " "), ISNULL( T1.NU_CNTA_BANC, " "),   
  ISNULL( T1.CO_CAJA, " "), ISNULL( T1.NU_COMP_CABA, " "), ISNULL( T1.NU_AMAR, " "), ISNULL( T1.NU_CANJ, " "),   
  ISNULL( T1.NU_RENO, " "), 0, ISNULL( T1.IM_CXCB, 0 ), ISNULL( T1.IM_PAGO, 0 ),   
  ISNULL( T1.FA_TIPO_CAMB, 0 ), T1.TI_DETA, "1", "1",   
  "1", T1.FE_REPO  
 FROM #TWDOCU_CLIE_Q35_1 T1, TMEMPR T2, TMAUXI_EMPR T4  
 Where T4.TI_AUXI_EMPR =  "' + @ISTI_AUXI_CLIE + '"  
 AND T1.FE_REPO >= CONVERT(DATETIME, "'+ @VSFE_INIC +'", 103)  
 AND T1.FE_REPO <= CONVERT(DATETIME, "'+ @VSFE_FINA +'", 103)  
 AND T1.CO_EMPR = T2.CO_EMPR  
 AND T1.CO_EMPR = T4.CO_EMPR  
 AND T1.CO_CLIE = T4.CO_AUXI_EMPR  
 UNION ALL  
 SELECT T1.CO_EMPR, MAX(T2.DE_NOMB), T1.CO_CLIE, MAX(T4.NO_AUXI),    
  " ", " ",  T1.CO_MONE, MAX(T5.SB_MONE),   
  MAX(T5.DE_MONE), " ", " ", " ",    
  " ", " ",  " ", " ",    
  " ", ISNULL( MAX(T3.IM_SAIN), 0 ), SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)),   
  0, "0", "2", "1",   
  "1", NULL    
 FROM #TWDOCU_CLIE_Q35_1 T1  
    INNER JOIN TMEMPR T2  
    ON T1.CO_EMPR = T2.CO_EMPR  
    LEFT OUTER JOIN #TWDOCU_CLIE_Q35_2 T3  
    ON  T1.CO_EMPR = T3.CO_EMPR  
 And T1.CO_CLIE = T3.CO_CLIE  
 And T1.CO_MONE = T3.CO_MONE  
    INNER JOIN TMAUXI_EMPR T4  
    ON  T4.TI_AUXI_EMPR = "' + @ISTI_AUXI_CLIE + '"   
    AND T1.CO_EMPR = T4.CO_EMPR  
 AND T1.CO_CLIE = T4.CO_AUXI_EMPR  
    INNER JOIN TTMONE T5  
    ON T1.CO_MONE = T5.CO_MONE  
 GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_MONE  
 UNION ALL  
  SELECT T1.CO_EMPR, MAX(T2.DE_NOMB), T1.CO_CLIE, MAX(T4.NO_AUXI),    
  " ", " ",  T1.CO_MONE, MAX(T5.SB_MONE),   
  MAX(T5.DE_MONE), " ", " ", " ",    
  " ", " ",  " ", " ",    
  " ", ISNULL( MAX(T1.IM_SAIN), 0 ), 0, 0,   
  0, "0", "2", "1",   
  "1", NULL    
 FROM #TWDOCU_CLIE_Q35_2 T1, TMEMPR T2, TMAUXI_EMPR T4, TTMONE T5  
 Where T4.TI_AUXI_EMPR = "' + @ISTI_AUXI_CLIE + '"   
 AND T1.CO_EMPR = T2.CO_EMPR  
 AND T1.CO_EMPR = T4.CO_EMPR  
 AND T1.CO_CLIE = T4.CO_AUXI_EMPR  
 AND T1.CO_MONE = T5.CO_MONE   
 AND NOT EXISTS (SELECT T3.CO_CLIE  
   FROM #TWDOCU_CLIE_Q35_1 T3  
   WHERE T1.CO_EMPR = T3.CO_EMPR  
   And T1.CO_CLIE = T3.CO_CLIE  
   And T1.CO_MONE = T3.CO_MONE )  
 GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_MONE  
 UNION ALL  
 SELECT T1.CO_EMPR, " ", " ", " ",   
  " ", " ",  T1.CO_MONE, MAX(T5.SB_MONE),   
  MAX(T5.DE_MONE), " ", " ", " ",    
  " ", " ",  " ", " ",    
  " ", ISNULL(MAX(T3.IM_SAIN), 0), SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)),   
  0, "0", "2", "2",   
  "1", NULL   
 FROM #TWDOCU_CLIE_Q35_1 T1  
    INNER JOIN TTMONE T5  
    ON T1.CO_MONE = T5.CO_MONE  
    LEFT OUTER JOIN (SELECT CO_EMPR, CO_MONE,  
      SUM(IM_SAIN) IM_SAIN  
      FROM #TWDOCU_CLIE_Q35_2   
      GROUP BY CO_EMPR, CO_MONE)T3  
    ON  T1.CO_EMPR = T3.CO_EMPR  
 And T1.CO_MONE = T3.CO_MONE   
 GROUP BY T1.CO_EMPR, T1.CO_MONE  
 UNION ALL  
 SELECT T1.CO_EMPR, " ", " ", " ",    
  " ", " ",  T1.CO_MONE, MAX(T5.SB_MONE),   
  MAX(T5.DE_MONE), " ", " ", " ",    
  " ", " ",  " ", " ",    
  " ", ISNULL( SUM(T1.IM_SAIN), 0 ), 0, 0,   
  0, "0", "2", "2",   
  "1", NULL  
 FROM #TWDOCU_CLIE_Q35_2 T1, TTMONE T5  
 Where T1.CO_MONE = T5.CO_MONE   
 AND NOT EXISTS (SELECT T3.CO_CLIE  
   FROM #TWDOCU_CLIE_Q35_1 T3  
   WHERE T1.CO_EMPR = T3.CO_EMPR  
   And T1.CO_MONE = T3.CO_MONE )  
 GROUP BY T1.CO_EMPR, T1.CO_MONE  
 UNION ALL  
 SELECT MAX(T1.CO_EMPR), " ", " ", " ",    
  " ", " ",  T1.CO_MONE, MAX(T5.SB_MONE),   
  MAX(T5.DE_MONE), " ", " ", " ",    
  " ", " ",  " ", " ",    
  " ", ISNULL(MAX(T3.IM_SAIN), 0), SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)),   
  0, "0", "2", "2",   
  "2", NULL  
 FROM #TWDOCU_CLIE_Q35_1 T1  
    INNER JOIN TTMONE T5  
    ON T1.CO_MONE = T5.CO_MONE   
    LEFT OUTER JOIN (SELECT CO_MONE, SUM(IM_SAIN) IM_SAIN  
      FROM #TWDOCU_CLIE_Q35_2   
      GROUP BY CO_MONE)T3  
    ON T1.CO_MONE = T3.CO_MONE   
 GROUP BY T1.CO_MONE   
 UNION ALL  
 SELECT MAX(T1.CO_EMPR), " ", " ", " ",    
  " ", " ",  T1.CO_MONE, MAX(T5.SB_MONE),   
  MAX(T5.DE_MONE), " ", " ", " ",    
  " ", " ",  " ", " ",    
  " ", ISNULL( SUM(T1.IM_SAIN), 0 ), 0, 0,   
  0, "0", "2", "2",   
  "2", NULL    
 FROM #TWDOCU_CLIE_Q35_2 T1, TTMONE T5  
 Where T1.CO_MONE = T5.CO_MONE   
 AND NOT EXISTS (SELECT T3.CO_CLIE  
   FROM #TWDOCU_CLIE_Q35_1 T3  
   WHERE T1.CO_MONE = T3.CO_MONE )  
 GROUP BY T1.CO_MONE ' + @VSTI_ORDE )  
  
END  
--CO_EMPR, DE_NOMB, TI_CLIE, DE_TIPO_CLIE,  
--CO_CLIE, NO_CORT_CLIE, IM_SAIN_NACI, IM_SAIN_EXTR  
  
  
IF @ISTI_REPO = 'R'   
BEGIN  
  
 DELETE #TWDOCU_CLIE_Q35_1  
 WHERE FE_REPO < @IDFE_EMIS_INIC  
  
 SELECT T1.CO_EMPR, MAX(T2.DE_NOMB), T1.CO_CLIE, MAX(T4.NO_AUXI),    
  T1.CO_MONE, MAX(T5.SB_MONE), MAX(T5.DE_MONE), ISNULL( MAX(T3.IM_SAIN), 0 ),   
  SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)), '1', '1'    
 FROM #TWDOCU_CLIE_Q35_1 T1  
    INNER JOIN TMEMPR T2  
    ON T1.CO_EMPR = T2.CO_EMPR  
    LEFT OUTER JOIN #TWDOCU_CLIE_Q35_2 T3  
    ON  T1.CO_EMPR = T3.CO_EMPR  
 And T1.CO_CLIE = T3.CO_CLIE  
 And T1.CO_MONE = T3.CO_MONE  
    INNER JOIN TMAUXI_EMPR T4  
    ON  T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE   
 AND T1.CO_EMPR = T4.CO_EMPR  
 AND T1.CO_CLIE = T4.CO_AUXI_EMPR  
    INNER JOIN TTMONE T5  
    ON T1.CO_MONE = T5.CO_MONE   
 GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_MONE  
 UNION ALL  
 SELECT T1.CO_EMPR, MAX(T2.DE_NOMB), T1.CO_CLIE, MAX(T4.NO_AUXI),    
  T1.CO_MONE, MAX(T5.SB_MONE), MAX(T5.DE_MONE), ISNULL( MAX(T1.IM_SAIN), 0 ),   
  0, 0, '1', '1'    
 FROM #TWDOCU_CLIE_Q35_2 T1, TMEMPR T2, TMAUXI_EMPR T4, TTMONE T5  
 Where T4.TI_AUXI_EMPR = @ISTI_AUXI_CLIE  
 AND T1.CO_EMPR = T2.CO_EMPR  
 AND T1.CO_EMPR = T4.CO_EMPR  
 AND T1.CO_CLIE = T4.CO_AUXI_EMPR  
 AND T1.CO_MONE = T5.CO_MONE   
 AND NOT EXISTS (SELECT T3.CO_CLIE  
   FROM #TWDOCU_CLIE_Q35_1 T3  
   WHERE T1.CO_EMPR = T3.CO_EMPR  
   And T1.CO_CLIE = T3.CO_CLIE  
   And T1.CO_MONE = T3.CO_MONE )  
 GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_MONE  
 UNION ALL  
 SELECT T1.CO_EMPR, '', '', '',    
  T1.CO_MONE, MAX(T5.SB_MONE), MAX(T5.DE_MONE), ISNULL(MAX(T3.IM_SAIN), 0),   
  SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)), '2', '1'   
 FROM #TWDOCU_CLIE_Q35_1 T1  
    INNER JOIN TTMONE T5  
    ON T1.CO_MONE = T5.CO_MONE   
    LEFT OUTER JOIN (SELECT CO_EMPR, CO_MONE,  
        SUM(IM_SAIN) IM_SAIN  
      FROM #TWDOCU_CLIE_Q35_2   
      GROUP BY CO_EMPR, CO_MONE)T3  
    ON  T1.CO_EMPR = T3.CO_EMPR  
 And T1.CO_MONE = T3.CO_MONE   
 GROUP BY T1.CO_EMPR, T1.CO_MONE  
 UNION ALL  
 SELECT T1.CO_EMPR, '', '', '',    
  T1.CO_MONE, MAX(T5.SB_MONE), MAX(T5.DE_MONE), ISNULL( SUM(T1.IM_SAIN), 0 ),   
  0, 0, '2', '1'    
 FROM #TWDOCU_CLIE_Q35_2 T1, TTMONE T5  
 Where T1.CO_MONE = T5.CO_MONE   
 AND NOT EXISTS (SELECT T3.CO_CLIE  
   FROM #TWDOCU_CLIE_Q35_1 T3  
   WHERE T1.CO_EMPR = T3.CO_EMPR  
   And T1.CO_MONE = T3.CO_MONE )  
 GROUP BY T1.CO_EMPR, T1.CO_MONE  
 UNION ALL  
 SELECT MAX(T1.CO_EMPR), '', '', '',   
  T1.CO_MONE, MAX(T5.SB_MONE), MAX(T5.DE_MONE), ISNULL(MAX(T3.IM_SAIN), 0),   
  SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)), '2', '2'  
 FROM #TWDOCU_CLIE_Q35_1 T1  
    INNER JOIN TTMONE T5  
    ON T1.CO_MONE = T5.CO_MONE   
    LEFT OUTER JOIN (SELECT CO_MONE, SUM(IM_SAIN) IM_SAIN  
      FROM #TWDOCU_CLIE_Q35_2   
      GROUP BY CO_MONE)T3  
    ON T1.CO_MONE = T3.CO_MONE   
 GROUP BY T1.CO_MONE   
 UNION ALL  
 SELECT MAX(T1.CO_EMPR), '', '', '',   
  T1.CO_MONE, MAX(T5.SB_MONE), MAX(T5.DE_MONE), ISNULL( SUM(T1.IM_SAIN), 0 ),   
  0, 0, '2', '2'    
 FROM #TWDOCU_CLIE_Q35_2 T1, TTMONE T5  
 Where T1.CO_MONE = T5.CO_MONE   
 AND NOT EXISTS (SELECT T3.CO_CLIE  
   FROM #TWDOCU_CLIE_Q35_1 T3  
   WHERE T1.CO_MONE = T3.CO_MONE )  
 GROUP BY T1.CO_MONE   
 ORDER BY 12, 1, 11, 4, 5  
  
END  
  
RETURN  
/*------------------------- FIN ------------------------------ */


GO
